package cn.xinfei.xdecision.data.core.util;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.cell.CellHandler;
import cn.xinfei.xdecision.common.model.datax.consts.VariableConst;
import cn.xinfei.xdecision.common.model.datax.exception.XDecisionDataException;
import cn.xinfei.xdecision.common.model.datax.exception.XDecisionDataExceptionType;
import cn.xinfei.xdecision.common.utils.util.StringUtil;
import cn.xinfei.xdecision.data.core.vo.CellDataVO;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class ExcelUtils {
    // 下载文件名称

    private static final String LIST_ID = "list_id";
    private static final String FROM = "from";
    private static final String SOURCE = "source";
    private static final String REASON = "reason";
    private static final String EXPIRY_DATE = "expiry_date";
    private static final String KEY_VALUE = "key_value";
    private static final String START_TIME = "start_time";
    private static final String END_TIME = "end_time";
    private static final String FILE_UPLOAD = "文件上传";

    public static final String INPUT_ELEMENT_ID = "input_element_id";
    public static final String OUT_FIELD_ELEMENT = "out_field_element";
    public static final String TYPE = "type";

	/**
	 * 一页一行
	 */
    public static void exportExcel(HttpServletResponse response, List<String> keys,String fileName) throws Exception {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        Sheet sheet = workbook.createSheet("Sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        Row row = sheet.createRow(0);
        for (int i = 0; i < keys.size(); i++) {
            Cell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(keys.get(i));
            cell.setCellValue(text);
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

	/**
	 * 多页一行
	 */
    public static void exportExcel(HttpServletResponse response, String fileName, List<String> sheetNames, List<List<String>> keysForSheets) throws Exception {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        for (int i = 0; i < sheetNames.size(); i++) {
            // 生成一个表格
            Sheet sheet = workbook.createSheet(sheetNames.get(i));
            List<String> keys = keysForSheets.get(i);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(15);
            Row row = sheet.createRow(0);
            for (int j = 0; j < keys.size(); j++) {
                Cell cell = row.createCell(j);
                XSSFRichTextString text = new XSSFRichTextString(keys.get(j));
                cell.setCellValue(text);
            }
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    public static void exportExcel(HttpServletResponse response, String fileName, List<String> sheetNames, Map<String, List<List<String>>> contentMap)
            throws Exception {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        for (int i = 0; i < sheetNames.size(); i++) {
            // 生成一个表格
            Sheet sheet = workbook.createSheet(sheetNames.get(i));
            List<List<String>> sheetContent = contentMap.get(sheetNames.get(i));
            for (int rowNum = 0; rowNum < sheetContent.size(); rowNum++) {
                List<String> rowContent = sheetContent.get(rowNum);
                // 设置表格默认列宽度为15个字节
                Row row = sheet.createRow(rowNum);
                sheet.setDefaultColumnWidth(15);
                for (int j = 0; j < rowContent.size(); j++) {
                    Cell cell = row.createCell(j);
                    XSSFRichTextString text = new XSSFRichTextString(rowContent.get(j));
                    cell.setCellValue(text);
                }
            }
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

	/**
	 * 前五行设置了背景色、边框、单元格大小、文本格式
	 */
	public static void exportExcelWithStyle(HttpServletResponse response, String fileName, String sheetName, List<List<String>> rowForSheets) throws Exception{
		// 声明一个工作薄
		XSSFWorkbook workbook = new XSSFWorkbook();

		Map<Integer,CellStyle> styles = createCellValues(workbook);
		// 生成一个表格
		Sheet sheet = workbook.createSheet(sheetName);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		sheet.setDefaultRowHeight((short) (25 * 20));
		CellStyle columnStyle = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		columnStyle.setDataFormat(format.getFormat("@"));
		for (int i = 0; i < rowForSheets.size(); i++) {
			Row row = sheet.createRow(i);
			List<String> keys = rowForSheets.get(i);
			for (int j = 0; j < keys.size(); j++) {
				//设置为文本格式
				sheet.setDefaultColumnStyle(j,columnStyle);

				Cell cell = row.createCell(j);
				cell.setCellStyle(styles.get(i));
				XSSFRichTextString text = new XSSFRichTextString(keys.get(j));
				cell.setCellValue(text);
			}
		}
		response.setContentType("application/octet-stream");
        response.setHeader("Access-Control-Expose-Headers","Content-disposition");
		response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
		response.flushBuffer();
		workbook.write(response.getOutputStream());
	}

	//设置单元格样式
	private static Map<Integer,CellStyle> createCellValues(XSSFWorkbook wb){
		Map<Integer, CellStyle> styles = new HashMap<>();

		Font firstRowFont = wb.createFont();
		firstRowFont.setFontHeightInPoints((short) 14);
		firstRowFont.setBold(true);
		CellStyle desStyle = wb.createCellStyle();
		desStyle.setFont(firstRowFont);
		desStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		CellStyle headStyle = wb.createCellStyle();
		headStyle.setBorderRight(BorderStyle.THIN);
		headStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		headStyle.setBorderLeft(BorderStyle.THIN);
		headStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		headStyle.setBorderTop(BorderStyle.THIN);
		headStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		headStyle.setBorderBottom(BorderStyle.THIN);
		headStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		headStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

		CellStyle textStyle = wb.createCellStyle();
		textStyle.setBorderRight(BorderStyle.THIN);
		textStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		textStyle.setBorderLeft(BorderStyle.THIN);
		textStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		textStyle.setBorderTop(BorderStyle.THIN);
		textStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		textStyle.setBorderBottom(BorderStyle.THIN);
		textStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());


		styles.put(0,desStyle);
		styles.put(1,headStyle);
		styles.put(2,textStyle);
		styles.put(3,headStyle);
		styles.put(4,textStyle);
		return styles;

	}

	/**
	 * 一页多行
	 */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<List<String>> rowForSheets) throws Exception {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        Sheet sheet = workbook.createSheet(sheetName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        for (int i = 0; i < rowForSheets.size(); i++) {
            Row row = sheet.createRow(i);
            List<String> keys = rowForSheets.get(i);
            for (int j = 0; j < keys.size(); j++) {
                Cell cell = row.createCell(j);
                XSSFRichTextString text = new XSSFRichTextString(keys.get(j));
                cell.setCellValue(text);
            }
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    public static JSONArray ReadExcelData(MultipartFile file, BigInteger listId) {
        JSONArray result = new JSONArray();
        if (file == null) {
            return null;
        }
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        String ext = fileName.substring(fileName.lastIndexOf("."));
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return null;
        }
        // 第一行，列名
        Row row = sheet.getRow(0);

        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = row.getCell(i).getStringCellValue();
        }
        // 得到总行数
        int rowNum = sheet.getPhysicalNumberOfRows();
        log.info("总行数 = " + rowNum);
        row = sheet.getRow(0);
        int colNum1 = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i < rowNum; i++) {
            JSONObject keyValue = new JSONObject();
            log.info("第 " + i + " 行");
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int j = 0;

            JSONObject json = new JSONObject();
            json.put(LIST_ID, listId);
            json.put(FROM, FILE_UPLOAD);

            String cellContent = "";

            while (j < colNum1) {
                String key = title[j];
                String value = getCellValue(row.getCell(j));

                if (!validateField(key, value)) {
                    throw new XDecisionDataException(
                            XDecisionDataExceptionType.LISTDETAIL_FILE_CONTENT_ERROR_CODE,
                            XDecisionDataExceptionType.LISTDETAIL_FILE_CONTENT_ERROR_CODE.text + ", 第" + (i + 1) + "行, 第" + (j + 1) + "列: " + key + ": " + value
                    );
                }

                cellContent += value;
                if (START_TIME.equals(key)) {
                    if (!StringUtils.isEmpty(value)) {
                        json.put(START_TIME, value);
                    }
                } else if (END_TIME.equals(key) || EXPIRY_DATE.equals(key)) {
                    if (!StringUtils.isEmpty(value)) {
                        if (value.equalsIgnoreCase("null")) {
                            json.put(END_TIME, null);
                        } else {
                            json.put(END_TIME, value);
                        }
                    }
                } else if (FROM.equals(key) || SOURCE.equals(key)) {
                    json.put(FROM, value);
                } else if (REASON.equals(key)) {
                    json.put(REASON, value);
                } else {
                    if (!StringUtils.isEmpty(value)) {
                        keyValue.put(key, value);
                    }
                }
                j++;
            }

            log.info("keyValue = " + keyValue);
            if (!StringUtils.isEmpty(cellContent) && keyValue.size() > 0) {
                json.put(KEY_VALUE, keyValue);
                result.add(json);
            }
        }
        return result;
    }



    /**
     * 方法测试
     *
     * @param filePath
     * @param listId
     * @return
     */
    public static List<JSONObject> ReadExcelDataTest(String filePath, BigInteger listId) {
        List<JSONObject> result = new ArrayList<>();
        Workbook wb = null;
        String ext = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return null;
        }
        // 第一行，列名
        Row row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        log.info("colNum: " + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = row.getCell(i).getStringCellValue();
        }
        // 得到总行数
        int rowNum = sheet.getPhysicalNumberOfRows();
        log.info("总行数 = " + rowNum);
        row = sheet.getRow(0);
        int colNum1 = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i < 100; i++) {
            JSONObject keyValue = new JSONObject();
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int j = 0;

            JSONObject json = new JSONObject();
            json.put(LIST_ID, listId);
            json.put(FROM, FILE_UPLOAD);

            String cellContent = "";

            while (j < colNum1) {
                String key = title[j];
                String value = getCellValue(row.getCell(j));

                if (!validateField(key, value)) {
                    throw new XDecisionDataException(
                            XDecisionDataExceptionType.LISTDETAIL_FILE_CONTENT_ERROR_CODE,
                            XDecisionDataExceptionType.LISTDETAIL_FILE_CONTENT_ERROR_CODE.text + ", 第" + (i + 1) + "行, 第" + (j + 1) + "列: " + key + ": " + value
                    );
                }

                cellContent += value;
                if (START_TIME.equals(key)) {
                    if (!StringUtils.isEmpty(value)) {
                        json.put(START_TIME, value);
                    }
                } else if (END_TIME.equals(key) || EXPIRY_DATE.equals(key)) {
                    if (!StringUtils.isEmpty(value)) {
                        if (value.equalsIgnoreCase("null")) {
                            json.put(END_TIME, null);
                        } else {
                            json.put(END_TIME, value);
                        }
                    }
                } else if (FROM.equals(key) || SOURCE.equals(key)) {
                    json.put(FROM, value);
                } else if (REASON.equals(key)) {
                    json.put(REASON, value);
                } else {
                    if (!StringUtils.isEmpty(value)) {
                        keyValue.put(key, value);
                    }
                }
                j++;
            }

            if (!StringUtils.isEmpty(cellContent) && keyValue.size() > 0) {
                json.put(KEY_VALUE, keyValue);
                log.info("第 " + i + " 行，jsonobject = " + json);
                result.add(json);
            }
        }
        return result;
    }

    public static boolean validateField(String key, String value) {
        if (StringUtils.isEmpty(value)) {
            return true;
        }
        switch (key) {
            case VariableConst.ID_CARD_NUMBER:
                String reg = "^\\d{15}$|^\\d{17}[0-9Xx]$";
                if (!value.matches(reg)) {
                    return false;
                }
                return true;
            case VariableConst.MOBILE:
                value = StringUtil.washPhoneNumber(value);
                return value.matches("[0-9]+") && (value.length() == 11);
            case START_TIME:
            case END_TIME:
            case EXPIRY_DATE:
                return isValidDate(value);
            default:
                return true;
        }
    }

    public static boolean isValidDate(String str) {
        if (StringUtils.isEmpty(str) || str.equalsIgnoreCase("null")) {
            return true;
        }
        boolean convertSuccess = true;
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            format.parse(str);
        } catch (ParseException e) {
            convertSuccess = false;
        }
        return convertSuccess;
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date theDate = cell.getDateCellValue();
                        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellValue = dff.format(theDate);
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case BLANK:
                    cellValue = "";
                    break;
                case ERROR:
                    cellValue = "";
                    break;
                default:
                    cellValue = cell.toString().trim();
                    break;
            }
        }
        return cellValue.trim();
    }

    /**
     * 读取表格数据
     *
     * @param templateFile   文件
     * @param startRowIndex  起始行号（从0开始）
     * @param endRowIndex    结束行号（从0开始）
     * @param startCellIndex 起始列号（从0开始）
     * @param endCellIndex   结束列号（从0开始）
     * @return 表格数据
     */
    public static List<List<CellDataVO>> readExcelData(InputStream templateFile, int startRowIndex, int endRowIndex, int startCellIndex, int endCellIndex) {
        ExcelReader excelReader = ExcelUtil.getReader(templateFile, 0);
        List<List<CellDataVO>> rowDataList = new ArrayList<>();
        AtomicInteger rowIndex = new AtomicInteger(-1);
        excelReader.read(startRowIndex, endRowIndex, new CellHandler() {
            @Override
            public void handle(Cell cell, Object value) {
                if (cell == null) {
                    //无单元格跳过
                    return;
                }
                if (cell.getColumnIndex() < startCellIndex || cell.getColumnIndex() > endCellIndex) {
                    //列号不在范围内跳过
                    return;
                }

                //新行的数据
                if (cell.getRowIndex() != rowIndex.get()) {
                    rowDataList.add(new ArrayList<>());
                }
                rowIndex.set(cell.getRowIndex());
                //取出新行数据对象存储单元格数据
                List<CellDataVO> cellDataList = rowDataList.get(rowDataList.size() - 1);
                CellDataVO cellData = new CellDataVO();
                cellData.setRowIndex(cell.getRowIndex());
                cellData.setCellIndex(cell.getColumnIndex());
                cellData.setValue(value);
                cellDataList.add(cellData);
            }
        });
        return rowDataList;
    }
}


